﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace SMS.dao
{
    public class ClazzDao
    {
        private static readonly ClazzDao INSTANCE = new ClazzDao();

        public static ClazzDao Instance
        {
            get
            {
                return INSTANCE;
            }
        }

        private ClazzDao() { }

        public DataTable findAll()
        {
            String sql = "select cla_id, cla_name " +
                         "from clazz " +
                         "group by cla_id,cla_name " +
                         "order by cla_name";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }
		
		//==================Quang
        public int insert(string clazzName, string clazzCapacity /*need to fix*/, int blockID)
        {
            String sql = "insert into clazz (cla_name, cla_capacity, blo_id) values " +
                         "(@clazzName, @clazzCapacity, @blo_id)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzName", clazzName));
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzCapacity", int.Parse(clazzCapacity)));
            DataFetcher.Instance.addParameter(new SqlParameter("@blo_id", blockID));
            return DataFetcher.Instance.execute();
        }
        
        public int update(String clazzName, string clazzCapacity /*need to fix*/, String clazzID) 
        {
            String sql = "update clazz set cla_name = @clazzName , cla_capacity = @clazzCapacity where cla_id = @clazzID";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzName", clazzName));
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzCapacity", int.Parse(clazzCapacity)));
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzID", Int32.Parse(clazzID)));
            return DataFetcher.Instance.execute();
        }
        
        public int deleteClazzInNull(int clazzID)
        {
            string sql = "delete from clazz where cla_id = @clazzID";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzID", clazzID));
            return DataFetcher.Instance.execute();
        }

        public int update(int clazzID, string clazzName,string clazzCapacity)
        {
            String sql = "update clazz set cla_name = @clazzName , cla_capacity = @clazzCapacity where cla_id = @clazzID";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzID", clazzID));
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzName", clazzName));
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzCapacity", int.Parse(clazzCapacity)));
            return DataFetcher.Instance.execute();
        }

        public DataTable findClazzName(string clazzName) 
        {
            String sql = "select cla_name from clazz where cla_name = @clazzName";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzName", clazzName));
            return DataFetcher.Instance.fetch();
        }

        public int getID(string clazzName)
        {
            String sql = "select cla_id, cla_name from clazz where cla_name = @clazzName";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzName", clazzName));
            return DataFetcher.Instance.getId();
        }

        public DataTable findClazzByBlock(int blockID) 
        {
            String sql =    "select c1.CLA_ID, c1.CLA_NAME, c1.CLA_CAPACITY, isnull(a.count, 0) as CLA_COUNTED " +
                            "from CLAZZ c1 left join " +
                            "(select c2.CLA_ID, c2.CLA_NAME, c2.CLA_CAPACITY, count(r.STU_ID) as count " +
                            "from CLAZZ c2, REGISTRATION r  where c2.CLA_ID = r.CLA_ID and c2.BLO_ID = @blockID " +
                            "group by c2.CLA_ID, c2.CLA_NAME, c2.CLA_CAPACITY) as a on a.CLA_ID = c1.CLA_ID " +
                            "where c1.BLO_ID = @blockID " +
                            "order by  c1.CLA_NAME ";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blockID", blockID));
            return DataFetcher.Instance.fetch();
        }
        
        public ArrayList findById(int clazzID)
        {
            String sql = "select * from clazz where cla_id = @id";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@id", clazzID));
            return DataFetcher.Instance.getOneRow();
        }
        



        //===================================
		
    }
}
